package com.qf.java2107.yb.hotel.dao.impl;

import com.qf.java2107.yb.hotel.dao.FoodDao;
import com.qf.java2107.yb.hotel.pojo.Food;
import com.qf.java2107.yb.hotel.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author yb
 * @version 1.0
 * @date 2021-10-26
 **/
public class FoodDaoImpl implements FoodDao {

    private QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());

    @Override
    public void save(Food food) throws SQLException {

        String sql = "INSERT INTO t_food (food_image) VALUES (?)";

        queryRunner.update(JdbcUtils.getConnection(), sql, food.getFoodImage());
    }

    @Override
    public List<Food> findByCondition(int start, int size, Integer typeId, String foodName) throws SQLException {

        List<Object> params = new ArrayList<>();

        StringBuffer sql = new StringBuffer("SELECT food_id foodId, type_id typeId, food_name foodName, food_price foodPrice, food_mprice foodMprice, food_image foodImage, food_desc foodDesc FROM t_food WHERE 1=1 ");

        if(typeId.intValue() != 0) {
            sql.append(" AND type_id = ? ");
            params.add(typeId);
        }
        sql.append(" AND food_name LIKE ? ");
        params.add(foodName);

        sql.append(" LIMIT ?,? ");
        params.add(start);

        params.add(size);

        System.out.println("findByCondition ==> " + sql.toString());

        return queryRunner.query(JdbcUtils.getConnection(), sql.toString(), new BeanListHandler<>(Food.class), params.toArray());
    }

    @Override
    public Long findByConditionCount(Integer typeId, String foodName) throws SQLException {

        StringBuffer sql = new StringBuffer("SELECT COUNT(1) foodDesc FROM t_food WHERE 1=1 ");

        List<Object> params = new ArrayList<>();
        if(typeId.intValue() != 0) {
            sql.append(" AND type_id = ? ");
            params.add(typeId);
        }
        sql.append(" AND food_name LIKE ? ");
        params.add(foodName);
        System.out.println("findByConditionCount --> " + sql.toString());

        return queryRunner.query(JdbcUtils.getConnection(), sql.toString(), new ScalarHandler<>(), params.toArray());
    }


    @Override
    public Food findById(Integer id) throws SQLException {

        String sql = "SELECT food_id foodId, type_id typeId, food_name foodName, food_price foodPrice,food_mprice foodMprice,food_image foodImage,food_desc foodDesc FROM `t_food` WHERE `food_id` = ?";

        return queryRunner.query(JdbcUtils.getConnection(),sql,new BeanHandler<>(Food.class),id);
    }

    @Override
    public int foodAdd(Food food) throws SQLException {

        String sql = "INSERT INTO t_food (type_id,food_name,food_price,food_mprice,food_image,food_desc) VALUES (?,?,?,?,?,?)";

        return queryRunner.update(JdbcUtils.getConnection(),sql,food.getTypeId(),food.getFoodName(),food.getFoodPrice(),food.getFoodMprice(),food.getFoodImage(),food.getFoodDesc());
    }

    @Override
    public List<Map<String, Object>> findAll() throws SQLException {

        String sql = "SELECT tFoodType.type_id typeId,tFoodType.type_name typeName,tFood.food_id foodId, tFood.type_id typeId,tFood.food_name foodName,tFood.food_price foodPrice,tFood.food_mprice foodMprice,tFood.food_image foodImage,tFood.food_desc foodDesc FROM t_food_type tFoodType,t_food tFood WHERE tFoodType.type_id=tFood.type_id";

        return queryRunner.query(JdbcUtils.getConnection(), sql, new MapListHandler());
    }

    @Override
    public int delete(Integer foodId) throws SQLException {

        String sql ="DELETE FROM `t_food` WHERE food_id=?";

        return queryRunner.update(JdbcUtils.getConnection(),sql,foodId);
    }

    @Override
    public List<Food> findLikeName(String foodName) throws SQLException {

        String sql = "SELECT food_id foodId,type_id typeId,food_name foodName,food_price foodPrice,food_mprice foodMprice,food_image foodImage,food_desc foodDesc FROM  t_food  WHERE  food_name LIKE ?";

        return queryRunner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(Food.class),foodName);
    }

    @Override
    public Food findByName(String foodName) throws SQLException {

        String sql = "SELECT food_id foodId,type_id typeId,food_name foodName,food_price foodPrice,food_mprice foodMprice,food_image foodImage,food_desc foodDesc FROM  t_food  WHERE  food_name = ?";

        return queryRunner.query(sql,new BeanHandler<>(Food.class),foodName);
    }

    @Override
    public void updateFood(Food food) throws SQLException {

        String sql = "UPDATE t_food SET `type_id`=?,`food_name`=?,`food_price`=?,`food_mprice`=? WHERE food_id = ?";

        queryRunner.update(JdbcUtils.getConnection(), sql, food.getTypeId(),food.getFoodName(),food.getFoodPrice(),food.getFoodMprice(),food.getFoodId());

    }
}
